﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using Wedn.Model.Data;
using Wedn.Utility;

namespace Wedn.DAL
{
    /// <summary>
    /// 拓展元数据表操作类(初步审核通过)
    /// 此类为抽象类, 不允许被实例化, 需要使用时直接调用
    /// </summary>
    public abstract class MetasDAO
    {
        #region 增加拓展元数据
        /// <summary>
        /// 向拓展元数据表中插入一行数据, 返回受影响行数
        /// </summary>
        /// <param name="meta">要插入的对象</param>
        /// <returns>受影响行数</returns>
        public static int Insert(Meta meta)
        {
            string sql = @"INSERT INTO [Blog_Metas]
                                    (
                                        [object_id],
                                        [object_type],
                                        [meta_key],
                                        [meta_value],
                                        [meta_desc]
                                    )
                                   VALUES 
                                    (
                                        @ObjectId,
                                        @ObjectType,
                                        @Key,
                                        @Value,
                                        @Description
                                    );";
            return ExecuteNonQuery(meta, sql);
        }
        #endregion

        #region 删除拓展元数据

        #region 删除一条数据
        /// <summary>
        /// 根据id删除对应的元数据记录, 返回受影响行数
        /// </summary>
        /// <param name="metaid">要删除记录的ID</param>
        /// <returns>受影响行数</returns>
        public static int Delete(int metaid)
        {
            string sql = "DELETE FROM [Blog_Metas] WHERE [meta_id]=@Id";
            return SqlHelper.ExecuteNonQuery(sql, new SqlParameter("@Id", metaid));
        }
        #endregion

        #region 删除多条数据
        /// <summary>
        /// 根据id数组删除对应的元数据记录, 返回受影响行数
        /// </summary>
        /// <param name="metaid">要删除记录的ID数组</param>
        /// <returns>受影响行数</returns>
        public static int Delete(int[] metaids)
        {
            string ids = string.Join(",", metaids);
            string sql = "DELETE FROM [Blog_Metas] WHERE [meta_id] in (" + ids + ");";
            return SqlHelper.ExecuteNonQuery(sql);
        }
        #endregion

        #endregion

        #region 更新拓展元数据
        /// <summary>
        /// 更新一个拓展元数据对象, 返回受影响行数
        /// </summary>
        /// <param name="meta">要更新的对象</param>
        /// <returns>受影响行数</returns>
        public static int Update(Meta meta)
        {
            string sql = @"UPDATE 
                                        [Blog_Metas]
                                   SET
                                        [object_id]=@ObjectId,
                                        [object_type]=@ObjectType,
                                        [meta_key]=@Key,
                                        [meta_value]=@Value,
                                        [meta_desc]=@Description
                                   WHERE
                                        [meta_id]=@Id;";
            return ExecuteNonQuery(meta, sql);
        }
        #endregion

        #region 选取拓展元数据

        #region 获取表中主键最大值
        /// <summary>
        /// 获取表中主键最大值
        /// </summary>
        /// <returns>主键最大值</returns>
        public static int GetMaxId()
        {
            return SqlHelper.SelectMaxId("meta_id", "Blog_Metas");
        }
        #endregion

        #region 选取全部拓展元数据
        /// <summary>
        /// 取出当前拓展元数据表中的所有拓展元数据信息
        /// </summary>
        /// <returns>包涵所有拓展元数据信息的数据表</returns>
        public static List<Meta> SelectMetas()
        {
            string sql = @"SELECT 
                                        [meta_id],
                                        [object_id],
                                        [object_type],
                                        [meta_key],
                                        [meta_value],
                                        [meta_desc]
                                    FROM
                                        [Blog_Metas];";
            return Select(sql);
        }
        #endregion

        #region 根据对象类型选取拓展元数据
        /// <summary>
        /// 取出当前类别下拓展元数据表中的所有拓展元数据信息
        /// </summary>
        /// <returns>包涵指定对象类型的所有拓展元数据信息的数据表</returns>
        public static List<Meta> SelectMetas(string objectType)
        {
            string sql = @"SELECT 
                                        [meta_id],
                                        [object_id],
                                        [object_type],
                                        [meta_key],
                                        [meta_value],
                                        [meta_desc]
                                    FROM
                                        [Blog_Metas]
                                    WHERE
                                        [object_type]=@Type;";
            return Select(sql, new SqlParameter("@Type", objectType));
        }
        #endregion

        #region 根据对象ID和对象类型选取拓展元数据
        /// <summary>
        /// 取出当前类别下拓展元数据表中的所有拓展元数据信息
        /// </summary>
        /// <returns>包涵指定对象类型的所有拓展元数据信息的数据表</returns>
        public static List<Meta> SelectMetas(string objectType, int objectId)
        {
            string sql = @"SELECT 
                                        [meta_id],
                                        [object_id],
                                        [object_type],
                                        [meta_key],
                                        [meta_value],
                                        [meta_desc]
                                    FROM
                                        [Blog_Metas]
                                    WHERE
                                        [object_type]=@Type;
                                    AND
                                        [object_id]=@Id";
            return Select(sql, new SqlParameter("@Type", objectType), new SqlParameter("@Id", objectId));
        }
        #endregion

        #region 根据ID选取全部拓展元数据对象
        /// <summary>
        /// 根据ID取出当前拓展元数据表中的拓展元数据对象
        /// </summary>
        /// <returns>拓展元数据对象</returns>
        public static Meta SelectMeta(int metaid)
        {
            string sql = @"SELECT 
                                        [meta_id],
                                        [object_id],
                                        [object_type],
                                        [meta_key],
                                        [meta_value],
                                        [meta_desc]
                                    FROM
                                        [Blog_Metas]
                                    WHERE
                                        [meta_id]=@Id;";
            return Select(sql, new SqlParameter("@Id", metaid))[0];
        }
        #endregion

        #endregion

        #region 私有操作方法

        #region select
        /// <summary>
        /// 私有选取数据方法
        /// </summary>
        /// <param name="sql">T-SQL语句</param>
        /// <param name="parameter">参数</param>
        /// <returns>数据集合</returns>
        private static List<Meta> Select(string sql, params SqlParameter[] parameter)
        {
            List<Meta> list = new List<Meta>();
            using (SqlDataReader reader = SqlHelper.ExecuteReader(sql, parameter))
            {
                while (reader.Read())
                {
                    Meta meta = new Meta(
                        reader.GetInt32(0),
                        reader.GetInt32(1),
                        reader.GetString(2),
                        reader.GetString(3),
                        reader.IsDBNull(4) ? null : reader.GetString(4),
                        reader.IsDBNull(5) ? null : reader.GetString(5));
                    list.Add(meta);
                }
            }
            return list;
        }
        #endregion

        #region ExecuteNonQuery
        /// <summary>
        /// 私有执行写入数据库操作
        /// </summary>
        /// <param name="meta">执行对象</param>
        /// <param name="sql">执行语句</param>
        /// <returns>受影响行数</returns>
        private static int ExecuteNonQuery(Meta meta, string sql)
        {
            return SqlHelper.ExecuteNonQuery(sql,
                new SqlParameter("@ObjectId", meta.ObjectId),
                new SqlParameter("@ObjectType", meta.ObjectType),
                new SqlParameter("@Key", meta.Key),
                new SqlParameter("@Value", string.IsNullOrEmpty(meta.Value) ? DBNull.Value : (object)meta.Value),
                new SqlParameter("@Description", string.IsNullOrEmpty(meta.Description) ? DBNull.Value : (object)meta.Description),
                new SqlParameter("@Id", meta.Id)
                );
        }
        #endregion

        #endregion
    }
}
